﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using NPOI.SS.UserModel;
using System.Text;
using NPOI.HSSF.UserModel;

namespace Utility
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class ExcelUtil
    {
        #region  excel导出旧方法
        ///// <summary>
        ///// 使用HSSFWorkbook生成数据到excel文件
        ///// </summary>
        ///// <param name="data">待持久化到excel的数据</param>
        ///// <param name="filePath">指定文件生成位置</param>
        //public static void Export(System.Data.DataTable data, string filePath)
        //{
        //    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        //    HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet");
        //    HSSFRow rowHeader = (HSSFRow)sheet.CreateRow(0);

        //    for (int i = 0; i < data.Columns.Count; i++)
        //    {
        //        rowHeader.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);
        //    }

        //    int rowIndex = 1;
        //    int totalCount = data.Rows.Count;//总条数
        //    int pageSize = 60000;//每页6万条
        //    int page = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1;//总页数

        //    foreach (System.Data.DataRow item in data.Rows)
        //    {
        //        HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex);
        //        for (int i = 0; i < data.Columns.Count; i++)
        //        {
        //            if (item[i] != null && !string.IsNullOrEmpty(item[i].ToString()))
        //            {
        //                row.CreateCell(i).SetCellValue(item[i].ToString());
        //            }
        //        }
        //        rowIndex++;
        //    }

        //    using (FileStream fs = File.Create(filePath))
        //    {
        //        hssfworkbook.Write(fs);
        //    }
        //}
        #endregion

        #region excel 分页导出新方法
        public static void Export(System.Data.DataTable data1, string filePath)
        {
            int totalCount = data1.Rows.Count;//总条数
            int pageSize = 60000;//每页6万条
            int page = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1;//总页数
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            for (int m = 1; m <= page; m++)
            {
                int n = 0;
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet" + m);
                HSSFRow rowHeader = (HSSFRow)sheet.CreateRow(0);

                for (int i = 0; i < data1.Columns.Count; i++)
                {
                    rowHeader.CreateCell(i).SetCellValue(data1.Columns[i].ColumnName);
                }

                int rowIndex = 1;

                foreach (System.Data.DataRow item in data1.Rows)
                {
                    if (n >= pageSize * (m - 1) && n < pageSize * m)
                    {
                        HSSFRow row = (HSSFRow)sheet.CreateRow(rowIndex);
                        for (int i = 0; i < data1.Columns.Count; i++)
                        {
                            if (item[i] != null && !string.IsNullOrEmpty(item[i].ToString()))
                            {
                                row.CreateCell(i).SetCellValue(item[i].ToString());
                            }
                        }
                        rowIndex++;
                    }
                    n++;
                }
            }
            using (FileStream fs = File.Create(filePath))
            {
                hssfworkbook.Write(fs);
            }
        }

        #endregion

        /// <summary>
        /// 将dt数据保存为excel(流),并行处理加锁
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="file">The file.</param>
        /// <exception cref="System.Exception">生成Excel: + file + 文件时发生异常！</exception>
        public static void DataTableToCsv(DataTable table, string file)
        {
            DataTableToCsv(table, file, Encoding.UTF8);
        }

        public static void DataTableToCsv(DataTable table, string file, Encoding encoding)
        {
            table.TableName = "ndy";
            string title = "";
            string seperator = "\t";
            foreach (DataColumn column in table.Columns)
            {
                if (column.ColumnName.Contains(seperator))
                {
                    seperator = ",";
                    break;
                }
            }
            string fileFolder = file.Substring(0, file.LastIndexOf('\\'));
            if (!Directory.Exists(fileFolder))
            {
                Directory.CreateDirectory(fileFolder);
            }

            using (FileStream fs = new FileStream(file, FileMode.OpenOrCreate))
            {
                using (StreamWriter sw = new StreamWriter(new BufferedStream(fs), encoding))
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        title += table.Columns[i].ColumnName + seperator; //栏位：自动跳到下一单元格
                    }

                    title = title.Substring(0, title.Length - 1) + "\n";
                    sw.Write(title);


                    for (int w = 0; w < table.Rows.Count; w++)
                    {
                        string line = "";

                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            line += table.Rows[w][i].ToString().Trim() + seperator; //内容：自动跳到下一单元格
                        }

                        line = line.Substring(0, line.Length - 1) + "\n";
                        sw.Write(line);

                    }

                    sw.Close();
                    fs.Close();
                }
            }

        }

        /// <summary>
        /// 将list泛型集合通过流直接保存为excel文件
        /// </summary>
        /// <typeparam name="T">数据集合中的数据类型</typeparam>
        /// <param name="list">泛型集合</param>
        /// <param name="filePath">文件路径</param>
        /// <returns>是否转换成功</returns>
        public static bool ListToCsv<T>(List<T> list, string filePath)
        {
            string file2 = filePath.Substring(0, filePath.LastIndexOf('\\'));
            if (!Directory.Exists(file2))
            {
                Directory.CreateDirectory(file2);
            }
            if (list == null || list.Count == 0)
            {
                return false;
            }
            Dictionary<PropertyInfo, string> disDisplayProperties = GetDisplayPropertiesByPD<T>();
            string title = "";
            string seperator = "\t";
            foreach (var item in disDisplayProperties)
            {
                if (item.Value.Contains(seperator))
                {
                    seperator = ",";
                    break;
                }
            }

            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
            {
                using (StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.UTF8))
                {
                    foreach (var item in disDisplayProperties)
                    {
                        string titleText = item.Value;

                        title += titleText + seperator; //栏位：自动跳到下一单元格

                    }

                    title = title.Substring(0, title.Length - 1) + "\n";
                    sw.Write(title);
                    for (int w = 0; w < list.Count; w++)
                    {
                        string line = "";
                        foreach (var item in disDisplayProperties)
                        {
                            var value = item.Key.GetValue(list[w], null);//当有空值时用空代替
                            if (value == null)
                            {
                                line += "" + seperator; //内容：自动跳到下一单元格
                            }
                            else
                            {
                                line += value.ToString().Trim() + seperator; //内容：自动跳到下一单元格
                            }
                        }
                        line = line.Substring(0, line.Length - 1) + "\n";
                        sw.Write(line);
                    }
                    sw.Close();
                    fs.Close();
                }
            }
            return true;
        }

        public static DataTable CsvToDataTable(string filePath, int top = 0)
        {
            return CsvToDataTable(filePath, Encoding.UTF8, top);
        }
        /// <summary>
        /// 将CSV文件读入DataTable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="top">截取数据的行数</param>
        /// <returns>
        /// 返回Csv文件对应的DataTable
        /// </returns>
        public static DataTable CsvToDataTable(string filePath, Encoding encoding, int top = 0)
        {
            return CsvToDataTable(filePath, encoding, 0, top);
        }
        /// <summary>
        /// CSVs to data table.
        /// </summary>
        /// <param name="filePath">The file path.</param>
        /// <param name="encoding">The encoding.</param>
        /// <param name="skip">默认为0，表示跳过几行</param>
        /// <param name="top">The top.</param>
        /// <returns></returns>
        public static DataTable CsvToDataTable(string filePath, Encoding encoding, int skip, int top)
        {
            if (!File.Exists(filePath))
            {
                return null;
            }

            string fileExt = CheckFileExtension(filePath, true);

            DataTable dt = new DataTable();
            string seperator = "\t";
            using (StreamReader reader = new StreamReader(filePath, encoding))
            {
                string firstLine = reader.ReadLine();
                if (!firstLine.Contains(seperator))
                {
                    seperator = ",";
                }
                string[] columns = Regex.Split(firstLine, seperator, RegexOptions.IgnoreCase);
                for (int i = 0; i < columns.Length; i++)
                {
                    DataColumn dc = new DataColumn(columns[i]);
                    dt.Columns.Add(dc);
                }
                //跳过
                for (int i = 0; i < skip; i++)
                {
                    reader.ReadLine();
                }
                int count = top <= 0 ? int.MaxValue : top;
                while (!reader.EndOfStream && count > 0)
                {
                    count--;
                    string line = reader.ReadLine();
                    columns = line.Split(new string[] { seperator }, StringSplitOptions.None);
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < columns.Length; i++)
                    {
                        dr[i] = columns[i];
                    }

                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }

        private static string Read(string filePath, Encoding encoding)
        {
            using (FileStream fileStream = File.OpenRead(filePath))
            {
                using (StreamReader reader = new StreamReader(fileStream,encoding))
                {
                    return reader.ReadToEnd();
                }
            }
        }

        public static DataTable CsvToDataTable1(string filePath, Encoding encoding, int top = 0)
        {
            if (!File.Exists(filePath))
            {
                return null;
            }

            string fileExt = CheckFileExtension(filePath, true);

            string fileContent = Read(filePath, encoding);

            //导入内存DataTable
            string[] fileLineArray = Regex.Split(fileContent, "\\n", RegexOptions.IgnoreCase);// strRead.Split( "/n" );
            DataTable dt = new DataTable();
            string seperator = "\t";
            if (!fileLineArray[0].Contains(seperator))
            {
                seperator = ",";
            }
            string[] columns = Regex.Split(fileLineArray[0], seperator, RegexOptions.IgnoreCase);

            for (int i = 0; i < columns.Length; i++)
            {
                DataColumn dc = new DataColumn(columns[i]);
                dt.Columns.Add(dc);
            }
            int readLineCount = top <= 0 ? fileLineArray.Length - 1 : top + 1;
            for (int r = 1; r < readLineCount; r++)
            {
                DataRow dr = dt.NewRow();
                columns = Regex.Split(fileLineArray[r], seperator, RegexOptions.IgnoreCase);
                for (int i = 0; i < columns.Length; i++)
                {
                    dr[i] = columns[i];
                }

                dt.Rows.Add(dr);

            }

            return dt;
        }

        #region 通过PropertyDescriptor获取

        /// <summary>
        /// 获取列的有效中文标示并过滤
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <returns>获取类型中标示为IsBrowsable的属性字典</returns>
        public static Dictionary<PropertyInfo, string> GetDisplayPropertiesByPD<T>()
        {
            Dictionary<PropertyInfo, string> disDisplayProperties = new Dictionary<PropertyInfo, string>();
            foreach (PropertyDescriptor item in TypeDescriptor.GetProperties(typeof(T)))
            {
                var browsable = item.IsBrowsable;
                if (browsable)
                {
                    var displayName = item.DisplayName;
                    disDisplayProperties.Add(typeof(T).GetProperty(item.Name), displayName);
                }
            }
            return disDisplayProperties;
        }
        #endregion

        /// <summary>
        /// 获取Excel数据保存到datatable
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <param name="sheetName">要读取excel的sheet名称</param>
        /// <param name="firstRowAsColumn">默认值为False。若为True，则Excel第一行作为表列名；若为False，则Excel第一行作为数据。</param>
        /// <param name="top">截取数据的行数</param>
        /// <returns>
        /// 返回读取到的文件内容数据集
        /// </returns>
        public static DataTable ConvertExcelToDataTable(string filePath, string sheetName, bool firstRowAsColumn = false, int top = 0)
        {
            if (!File.Exists(filePath))
            {
                return null;
            }

            string fileExt = CheckFileExtension(filePath, false);

            string connectionString = GetConnectionStringByExcelExtension(filePath, fileExt, firstRowAsColumn);

            DataTable dataTable = new DataTable();
            //DataSet ds = new DataSet();
            // 使用 ACE.OLEDB获取临时文件excel数据集
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                // 获取站点信息sheet对应的数据集
                conn.Open();

                string queryString = string.Concat("select * from [", sheetName, "$]");

                using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(queryString, conn))
                {
                    //dataAdapter.Fill(dataTable);
                    if (top > 0)
                    {
                        dataAdapter.Fill(0, firstRowAsColumn ? top + 1 : top, dataTable);
                    }
                    else
                    {
                        dataAdapter.Fill(dataTable);
                    }
                    dataAdapter.Dispose();
                }
            }

            return dataTable;
        }

        private static string GetConnectionStringByExcelExtension(string filePath, string fileExt, bool firstRowAsColumn)
        {
            string connectionString = string.Empty;
            if (fileExt.ToLower() == "xls")
            {
                connectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'", filePath, firstRowAsColumn ? "Yes" : "No");
            }
            else
            {
                connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR={1};IMEX=1;'", filePath, firstRowAsColumn ? "Yes" : "No");
            }
            return connectionString;
        }

        /// <summary>
        /// 获取Excel数据保存到datatable
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <param name="sheetName">要读取excel的sheet名称</param>
        /// <param name="firstRowAsColumn">默认值为False。若为True，则Excel第一行作为表列名；若为False，则Excel第一行作为数据。</param>
        /// <param name="top">截取数据的行数</param>
        /// <returns>
        /// 返回读取到的文件内容数据集
        /// </returns>
        public static DataTable ExcelToDataTable(string filePath, string sheetName, bool firstRowAsColumn = false, int top = 0)
        {
            if (!File.Exists(filePath))
            {
                return null;
            }

            string fileExt = CheckFileExtension(filePath, false);

            IWorkbook workbook = WorkbookFactory.Create(filePath);
            ISheet sheet = workbook.GetSheet(sheetName);

            IRow headerRow = sheet.GetRow(sheet.FirstRowNum);

            int startDataRow = sheet.FirstRowNum;

            DataTable dt = new DataTable();
            if (firstRowAsColumn)
            {
                for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
                {
                    ICell cell = headerRow.GetCell(i);
                    dt.Columns.Add(new DataColumn() { ColumnName = cell.StringCellValue });
                }
                startDataRow++;
            }
            else
            {
                for (int i = headerRow.FirstCellNum; i < headerRow.LastCellNum; i++)
                {
                    dt.Columns.Add(new DataColumn() { ColumnName = string.Format("F{0}", i) });
                }
            }
            int endDataRow = sheet.LastRowNum;
            if (top > 0)
            {
                endDataRow = startDataRow + top - 1;
            }

            for (int i = startDataRow; i <= endDataRow; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                    continue;
                DataRow dr = dt.NewRow();
                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    ICell cell = row.GetCell(j);
                    if (cell != null)
                    {
                        dr[j] = GetCellValue(cell);
                    }
                }
                dt.Rows.Add(dr);
            }
            dt.TableName = sheetName;
            return dt;
        }

        private static object GetCellValue(ICell cell)
        {
            if (cell == null)
                throw new ArgumentException("cell");
            CellType cellType = cell.CellType;
            if (cellType == CellType.Formula)
            {
                cellType = cell.CachedFormulaResultType;
            }
            switch (cellType)
            {
                case CellType.Blank:
                    return DBNull.Value;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue;
                    }
                    else
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                default:
                    return cell.ToString();
            }
        }

        private static string CheckFileExtension(string filePath, bool includingCsv)
        {
            string fileExt = Path.GetExtension(filePath).ToLower().TrimStart('.');

            if (fileExt != "xls" && fileExt != "xlsx")
            {
                if (includingCsv)
                {
                    if (fileExt != "csv" && fileExt != "ne")
                    {
                        throw new ArgumentException("文件后缀名不是xls、xlsx或者csv", filePath);
                    }
                }
                else
                {
                    throw new ArgumentException("文件后缀名不是xls或者xlsx", filePath);
                }
            }
            return fileExt;
        }

        /// <summary>
        /// 获取Excel文件所有的Sheet名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>所有的Sheet名称</returns>
        /// <exception cref="System.ArgumentException">文件后缀名不是xls或者xlsx</exception>
        public static List<string> GetAllSheetNames(string filePath)
        {
            if (!File.Exists(filePath))
            {
                return new List<string>();
            }

            string fileExt = CheckFileExtension(filePath, false);
            IWorkbook workbook = WorkbookFactory.Create(filePath);
            List<string> sheetNameList = new List<string>();
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                sheetNameList.Add(workbook.GetSheetName(i));
            }
            return sheetNameList;
        }

        private static string GetConnectionStringByExcelExtension(string filePath, string fileExt)
        {
            string connectionString = string.Empty;
            if (fileExt.ToLower() == "xls")
            {
                connectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", filePath);
            }
            else
            {
                connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=NO;IMEX=1;'", filePath);
            }
            return connectionString;
        }

        private static string CheckFileExtension(string filePath)
        {
            string fileExt = Path.GetExtension(filePath).ToLower().TrimStart('.');
            if (fileExt != "xls" && fileExt != "xlsx")
            {
                throw new ArgumentException("文件后缀名不是xls或者xlsx", filePath);
            }
            return fileExt;
        }
        /// <summary>
        /// 获取Excel文件所有的Sheet名称
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns>所有的Sheet名称</returns>
        /// <exception cref="System.ArgumentException">文件后缀名不是xls或者xlsx</exception>
        public static List<string> GetAllSheetNamesOleDb(string filePath)
        {
            if (!File.Exists(filePath))
            {
                return new List<string>();
            }

            string fileExt = CheckFileExtension(filePath);

            string connectionString = GetConnectionStringByExcelExtension(filePath, fileExt);

            List<string> sheetNameList = new List<string>();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();
                var sheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,   //检索excel的架构信息
                                           new[] { null, null, null, "Table" });

                for (int i = 0; i < sheetName.Rows.Count; i++)
                {
                    string name = sheetName.Rows[i]["TABLE_NAME"].ToString();
                    if (!name.Contains("FilterDatabase"))
                    {
                        sheetNameList.Add(name);
                    }
                }
            }
            return sheetNameList;
        }

    }
}
